package com.lubase.metadata.mapper;

import com.lubase.metadata.model.ColumnMetaModel;
import com.lubase.metadata.model.ConstraintMetaModel;
import com.lubase.metadata.model.TableMetaModel;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * 多数据库测试mapper
 */
@Mapper
public interface SqlserverMetadataReadMapper {

    @Select("SELECT  s.name+'.'+ t.name as table_name,ep.value AS table_comment FROM\n" +
            "                                            sys.tables t left JOIN sys.extended_properties ep ON t.object_id = ep.major_id AND ep.minor_id = 0  JOIN   sys.schemas s ON t.schema_id = s.schema_id\n" +
            "                                            where t.type='U'")
    @Results({
            @Result(property = "tableName", column = "table_name"),
            @Result(property = "tableComment", column = "table_comment")
    })
    List<TableMetaModel> getTableList(String databaseCode);


    @Select("SELECT\n" +
            "    (table_schema+'.'+TABLE_NAME) table_name,\n" +
            "    COLUMN_NAME column_name,\n" +
            "    (\n" +
            "        SELECT\n" +
            "            VALUE\n" +
            "        FROM\n" +
            "            sys.extended_properties\n" +
            "        WHERE\n" +
            "            major_id = OBJECT_ID(TABLE_NAME)\n" +
            "            AND minor_id = COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME,'ColumnId')\n" +
            "            AND name = 'MS_Description'\n" +
            "    ) AS column_comment,\n" +
            "    DATA_TYPE+(CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN '('+CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR)+')' ELSE '' END) as column_type\n" +
            "FROM\n" +
            "    INFORMATION_SCHEMA.COLUMNS where table_name in (select  name from sys.tables)\n")
    @Results({
            @Result(property = "tableName", column = "table_name"),
            @Result(property = "columnName", column = "column_name"),
            @Result(property = "columnComment", column = "column_comment"),
            @Result(property = "columnType", column = "column_type")
    })
    List<ColumnMetaModel> getTableColumnListByDatabase(@Param("databaseCode") String databaseCode);

    @Select("SELECT\n" +
            "    f.name AS constraint_name,\n" +
            "    s.name+'.'+OBJECT_NAME(f.parent_object_id) AS table_name,\n" +
            "    COL_NAME(fc.parent_object_id,fc.parent_column_id) AS column_name,\n" +
            "    s.name+'.'+OBJECT_NAME (f.referenced_object_id) AS referenced_table_name,\n" +
            "    COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS referenced_column_name\n" +
            "FROM\n" +
            "    sys.foreign_keys AS f\n" +
            "    INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id\n" +
            "    INNER JOIN sys.objects AS o ON f.referenced_object_id = o.OBJECT_ID" +
            "    INNER JOIN   sys.schemas s ON o.schema_id = s.schema_id")
    @Results({
            @Result(property = "constraintName", column = "constraint_name"),
            @Result(property = "tableName", column = "table_name"),
            @Result(property = "columnName", column = "column_name"),
            @Result(property = "referencedTableName", column = "referenced_table_name"),
            @Result(property = "referencedColumnName", column = "referenced_column_name")
    })
    List<ConstraintMetaModel> getTableConstraintList(@Param("databaseCode") String databaseCode);
}
